clear all
set more off

****************************************************************
//// 08-PROCESS_VIOLATION: CONSTRUCT VIOLATION INFORMATION ////
***************************************************************


// Get UTC Appendix C Data -------
import excel using "${raw}/violation/utc_appendix_c_CLEAN_FLAG.xlsx", clear first
ren class viol_class
ren disp viol_disp
ren points viol_points
ren sus viol_sus
ren fine viol_fine
ren flag viol_flag
ren description viol_description
keep viol_code viol_description viol_class viol_disp viol_sus viol_points viol_fine viol_flag
order viol_code viol_description viol_class viol_disp viol_sus viol_points viol_fine viol_flag
destring viol_code, replace force

sort viol_code viol_fine
duplicates drop viol_code, force 

tempfile PART1
save `PART1'


// Get old version of codes -----------
import excel using "${raw}/violation/viol_codes.xlsx", clear first 
keep Viol_Code GROUP
ren GROUP Viol_Group
replace Viol_Group=trim(upper(Viol_Group))
rename Viol_Code viol_code 
ren Viol_Group viol_group_old
tempfile PART2 
save `PART2'


use "${temp}/process-citation", clear
keep Viol_Code 
rename Viol_Code viol_code 
drop if mi(viol_code)
duplicates drop


/// MERGE ////
merge 1:1 viol_code using `PART1', keep(1 3) nogen
merge 1:1 viol_code using `PART2', keep(1 3) nogen


/// CLEAN UP ////
ren viol_sus viol_sus_orig
gen viol_sus = !mi(viol_sus_ori)
drop viol_sus_orig
destring viol_points, replace force
destring viol_fine, replace force
order viol_code viol_desc viol_disp viol_sus

/// LABELS ////
label var viol_description "Viol Description"
label var viol_class "Viol Class"
label var viol_disp "Viol Disposition"
label var viol_sus "Viol Suspension"
label var viol_points "Viol Points"
label var viol_fine "Viol Fine"
label var viol_flag "Viol Type Flag"
label var viol_group_old "Viol Group (OLD VERSION)"

/// STORE ///
qui compress
saveold "${temp}/violation", replace




